<?php

namespace App\Console\Commands;

use App\Http\Services\Oss;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use PHPExcel;
use PHPExcel_IOFactory;

class CommandCreateNewYearJdExcel extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'CreateNewYearJdExcel';

    protected $description = '生成每日金豆数据';

    protected $successLog = 'CreateNewYearExcel'; //日志
    protected $errorLog = 'error_CreateNewYearExcel'; //错误日志

    protected $apiCon = 'api'; //数据库
    protected $activityConn = 'activity';

    protected $newYearStream = 'new_year_2024_stream';//年度送礼表
    protected $user = 'user';
    protected $union_user = 'union_user';
    protected $union = 'union';
    protected $jd_stream_new_year = 'new_year_2024118_jd_log';
    protected $excelOutTable = 'new_year_excel_2024';
    protected $jdTable = 'new_year_2024118_jd_log';

    protected $activityStart = '1706090400';//2024-01-24 18:00:00
    protected $activityEnd = '1706724000';//2024-02-01 02:00:00

    protected $oss;
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->oss = new Oss();
        parent::__construct();
        
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        do{
            if(time() < $this->activityStart || time() > $this->activityEnd){
                write_log($this->successLog,'活动结束',[]);
                break;
            }
            //查询准备要生成的魅力日榜数据
            $yestorDayStart = Carbon::today()->startOfDay()->toDateTimeString();
            $yestorDayEnd =  Carbon::today()->endOfDay()->toDateTimeString();
            $needDatasCount = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->count();
            if($needDatasCount == 0){
                write_log($this->successLog,'无数据',[]);
                break;
            }
            //领取金豆数
            $JdRes = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //领取人数
            $JdUserRes = DB::connection($this->activityConn)->table($this->jdTable)
            ->select(DB::raw('count(distinct(user_id)) as count'))
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->first();
            $JdUserRes = objToArray($JdUserRes);
            //日任务1领取金豆数量
            $dailyOne = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',1)
            ->where('jd_value',10)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //日任务2领取金豆数量
            $dailyTwo = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',1)
            ->where('jd_value',25)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //日任务3领取金豆数量
            $dailyThree = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',1)
            ->where('jd_value',66)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //日任务4领取金豆数量
            $dailyFour = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',1)
            ->where('jd_value',88)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //日任务5领取金豆数量
            $dailyFive = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',1)
            ->where('jd_value',128)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //累计任务1领取金豆数量
            $allOne = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',2)
            ->where('jd_value',166)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //累计任务2领取金豆数量
            $allTwo = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',2)
            ->where('jd_value',288)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //累计任务3领取金豆数量
            $allThree = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',2)
            ->where('jd_value',520)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //累计任务4领取金豆数量
            $allFour = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',2)
            ->where('jd_value',1288)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
            //累计任务1领取金豆数量
            $allFive = DB::connection($this->activityConn)->table($this->jdTable)
            ->where('type',2)
            ->where('jd_value',1888)
            ->where('created_at','>=',$yestorDayStart)
            ->where('created_at','<=',$yestorDayEnd)
            ->sum('jd_value');
        
            $excelData[0] = [
                'date' => Carbon::yesterday()->toDateString(),//日期
                'user_count' => $JdUserRes['count'],//领取奖励人数
                'jd_count' => $JdRes,//领取金豆数量
                'dayOne' => $dailyOne,//日1
                'dayTwo' => $dailyTwo,//日2
                'dayThree' => $dailyThree,//日3
                'dayFour' => $dailyFour,//日4
                'dayFive' => $dailyFive,//日5
                'allOne' => $allOne,//累1
                'allTwo' => $allTwo,//累2
                'allThree' => $allThree,//累3
                'allFour' => $allFour,//累4
                'allFive' => $allFive,//累5
            ]; 
            //生成Excel
            $fileres = $this->CreateExcel($excelData,'jd.xlsx',1);
            if($fileres){
                //上传oss
                $url = $this->upLoadOss($fileres['fileName']);
                if($url){
                    //删除文件
                    if (file_exists(storage_path('jd.xlsx'))) {
                        unlink(storage_path('jd.xlsx'));     //删除
                    }
                    
                    $insert = [
                        'type' => 6,
                        'excel_url' => $url,
                        'created_at' => Carbon::now()->toDateTimeString()
                    ];
                    DB::beginTransaction();
                    try{
                        $insertGetId = DB::connection($this->activityConn)->table($this->excelOutTable)->insertGetId($insert);
                        DB::commit();
                        return true;
                        write_log($this->successLog,'INFO 数据生成成功',$insertGetId);
                    }catch(\Exception $e){
                        DB::rollBack();
                        write_log($this->errorLog,'ERROR 数据错误',['msg'=>$e->getMessage(),'data'=>$insert]);
                        return false;
                    }
                }
            }else{
                write_log($this->errorLog,'ERROR 数据Excel失败',$excelData);  
            }


        }while(false);
        
    }

    //excel 生成
    public function CreateExcel($csvData, $fileName='data.excel', $type=1){
        //生成EXCEL
        $objPHPExcel = new PHPExcel();
        try {
            $objSheet = $objPHPExcel->getActiveSheet();
            if($type == 1){
                $this->excelTemp($objSheet, $objPHPExcel, $csvData);
            }
                
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $filePath = storage_path($fileName);
            $objWriter->save($filePath);
            
            return ['fileName' => $fileName, 'filePath' => $filePath];
        } catch (\PHPExcel_Exception $e) {
            return false;
            write_log($this->successLog,'生成Excel数据失败',[$e]);
        }
    }

    //上传OSS
    public function upLoadOss($fileName=''){
        if(empty($fileName)){
            write_log($this->successLog,'文件不存在',[]);
            return false;
        }
        $upload_config =  curl_request(config('kkaudio.kkaudio_api') . "/inner/system/put_file_url", ['type' => 24, 'exts' => 'xlsx', 'noase' => 1], $contentType = 'urlencoded', $httpMethod = 'GET');

        $uri = $upload_config['data'][0]['uri'];
        $formatUri = ltrim($uri, '/');
        $localFilePath = storage_path($fileName);
        $ossRes = $this->oss->uploadFile($formatUri, $localFilePath);
        if (!$ossRes) {
           write_log($this->errorLog, '上传文件过程中发生错误',$ossRes);
           return false;
        }
        $url = '/'.$formatUri;
        return $url;
    }

    //魅力日榜
    public function excelTemp($objSheet, $objPHPExcel, $csvData){
        //工作表标题
        $objSheet->setTitle("魅力日榜数据");
        //第一行内容，放列标题
        $objSheet->setCellValue("A1", "日期");
        $objSheet->setCellValue("B1", "领取人数");
        $objSheet->setCellValue("C1", "领取金豆数");
        $objSheet->setCellValue("D1", "日任务1");
        $objSheet->setCellValue("E1", "日任务2");
        $objSheet->setCellValue("F1", "日任务3");
        $objSheet->setCellValue("G1", "日任务4");
        $objSheet->setCellValue("H1", "日任务5");
        $objSheet->setCellValue("I1", "累计任务1");
        $objSheet->setCellValue("J1", "累计任务2");
        $objSheet->setCellValue("K1", "累计任务3");
        $objSheet->setCellValue("L1", "累计任务4");
        $objSheet->setCellValue("M1", "累计任务5");
        //设置第一行的背景颜色
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:M1')
            ->getFill()
            ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB('FFFF00');
        //后面行循环放内容
        for($i=0;$i<count($csvData);$i++){
            $lineNum = $i+2;
            $objSheet->setCellValue("A".$lineNum, $csvData[$i]['date']);
            $objSheet->setCellValue("B".$lineNum, $csvData[$i]['user_count']);
            $objSheet->setCellValue("C".$lineNum, $csvData[$i]['jd_count']);
            $objSheet->setCellValue("D".$lineNum, $csvData[$i]['dayOne']);
            $objSheet->setCellValue("E".$lineNum, $csvData[$i]['dayTwo']);
            $objSheet->setCellValue("F".$lineNum, $csvData[$i]['dayThree']);
            $objSheet->setCellValue("G".$lineNum, $csvData[$i]['dayFour']);
            $objSheet->setCellValue("H".$lineNum, $csvData[$i]['dayFive']);
            $objSheet->setCellValue("I".$lineNum, $csvData[$i]['allOne']);
            $objSheet->setCellValue("J".$lineNum, $csvData[$i]['allTwo']);
            $objSheet->setCellValue("K".$lineNum, $csvData[$i]['allThree']);
            $objSheet->setCellValue("L".$lineNum, $csvData[$i]['allFour']);
            $objSheet->setCellValue("M".$lineNum, $csvData[$i]['allFive']);
        }
    }
}